package com.gamota.mobi.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.gamota.mobi.ListingActivity.InstalledAppItem;
import com.gamota.mobi.model.ContentItem;
import com.gamota.mobi.model.DownloadLaterItem;
import com.gamota.mobi.model.GiftSetting;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	private static final int DATABASE_VERSION = 17;
	// Database Name
	private static final String DATABASE_NAME = "AppStoreVn.db";
	// table download later
	private static final String TABLE_DOWNLOAD_LATER = "Download_Later";
	private static final String KEY_ID = "id";
	private static final String KEY_SLUG = "slug";
	private static final String KEY_NAME = "name";
	private static final String KEY_AUTHOR = "author";
	private static final String KEY_ICON = "icon_url";
	private static final String KEY_PRICE_1 = "price1";
	private static final String KEY_PRICE_2 = "price2";
	//table for update content
	private static final String TABLE_DOWNLOADED_CONTENT = "Downloaded_Content";
	private static final String KEY_TYPE = "type";
	private static final String KEY_VERSION = "version";
	private static final String KEY_TITLE = "name";
	private static final String KEY_PATH = "path";
	private static final String KEY_VERISON_NUMBER = "version_number";
	
	private static final String TABLE_INSTALLED_APPS = "InstalledApps";
	private static final String KEY_PACKAGE_NAME = "package_name";
	private static final String KEY_APP_VERSION = "app_version";
	
	private static final String TABLE_RECENT_SEARCH = "Recent_Search";
	private static final String KEY_QUERY = "query";
	
	private static final String TABLE_GIFT = "os_metadata";
	private static final String KEY_APP_NAME = "package_metadata";
	private static final String KEY_DAYS = "dtg_metadata";
	private static final String KEY_RECEIVE = "is_rec_metadata";
	private static final String KEY_TIME_TO_RECIEVE = "ttr_metadata";
	
	private static final String TABLE_GIFT_SYNC = "system_metadata";
	private static final String KEY_IS_RECEIVE = "system_storage";
	private static final String KEY_GIFT_NAME = "system_data";
	
	public DatabaseHelper(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String CREATE_TABLE_DOWNLOAD_LATER = "CREATE TABLE " + TABLE_DOWNLOAD_LATER + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_SLUG + " TEXT,"
                + KEY_NAME + " TEXT," + KEY_AUTHOR + " TEXT," + KEY_ICON + " TEXT," + KEY_PRICE_1 + " TEXT," + KEY_PRICE_2 + " TEXT," + KEY_PACKAGE_NAME + " TEXT," + KEY_TYPE + " TEXT)";
		
		String CREATE_TABLE_DOWNLOADED = "CREATE TABLE " + TABLE_DOWNLOADED_CONTENT + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_SLUG + " TEXT,"
                + KEY_TYPE + " TEXT," + KEY_VERSION + " TEXT," + KEY_VERISON_NUMBER + " INTEGER," + KEY_TITLE + " TEXT," + KEY_PATH + " TEXT" + ")";
		
		String CREATE_TABLE_SEARCH = "CREATE TABLE " + TABLE_RECENT_SEARCH + "(" + KEY_QUERY + " TEXT)";
		
		String CREATE_TABLE_INSTALLED_APPS = "CREATE TABLE " + TABLE_INSTALLED_APPS + "(" + KEY_PACKAGE_NAME + " TEXT," + KEY_APP_VERSION + " TEXT)";
		
		String CREATE_TABLE_GIFT = "CREATE TABLE " + TABLE_GIFT + "(" + KEY_APP_NAME + " TEXT," + KEY_SLUG + " TEXT," + KEY_TITLE + " TEXT," + KEY_ICON + " TEXT," + KEY_DAYS + " TEXT," + KEY_TIME_TO_RECIEVE + " TEXT," + KEY_RECEIVE + " TEXT)";
		
		String CREATE_TABLE_GIFT_SYNC = "CREATE TABLE " + TABLE_GIFT_SYNC + "(" + KEY_GIFT_NAME + " TEXT," + KEY_IS_RECEIVE + " TEXT)";
		
		db.execSQL(CREATE_TABLE_DOWNLOAD_LATER);
		db.execSQL(CREATE_TABLE_DOWNLOADED);
		db.execSQL(CREATE_TABLE_SEARCH);
		db.execSQL(CREATE_TABLE_INSTALLED_APPS);
		db.execSQL(CREATE_TABLE_GIFT);
		db.execSQL(CREATE_TABLE_GIFT_SYNC);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_DOWNLOAD_LATER);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_DOWNLOADED_CONTENT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECENT_SEARCH);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_INSTALLED_APPS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_GIFT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_GIFT_SYNC);
        onCreate(db);
	}
	
	public void syncGift(List<GiftSetting> list){
		SQLiteDatabase db = this.getWritableDatabase();
	    ContentValues values;
	    for(GiftSetting gift : list){
	    	values = new ContentValues();
	    	values.put(KEY_GIFT_NAME, gift.getSlug());
		    values.put(KEY_IS_RECEIVE, gift.getReceived());
		    db.insert(TABLE_GIFT_SYNC, null, values);
	    }
	    db.close();
	}
	
	public void addGiftSync(GiftSetting gift){
		SQLiteDatabase db = this.getWritableDatabase();
	    ContentValues values;
	    values = new ContentValues();
	    values.put(KEY_GIFT_NAME, gift.getSlug());
		values.put(KEY_IS_RECEIVE, gift.getReceived());
		db.insert(TABLE_GIFT_SYNC, null, values);
	    db.close();
	}
	
	public void truncateTableGiftSync(){
		String auery = "DELETE FROM " + TABLE_GIFT_SYNC;
	    SQLiteDatabase db = this.getWritableDatabase();
	    db.execSQL(auery);
	    db.close();
	}
	
	public GiftSetting getGiftSync(String slug){
		String selectQuery = "SELECT * FROM " + TABLE_GIFT_SYNC + " WHERE " + KEY_GIFT_NAME + " = '" + slug + "'";
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    GiftSetting gift = null;
	    if (cursor.moveToFirst()) {
	        do {
	        	gift = new GiftSetting();
	            gift.setReceived(cursor.getInt(cursor.getColumnIndex(KEY_IS_RECEIVE)));
	            gift.setSlug(cursor.getString(cursor.getColumnIndex(KEY_GIFT_NAME)));
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return gift;
	}
	
	public void addGift(GiftSetting gift){
		if(!checkGiftExists(gift.getName())){
			SQLiteDatabase db = this.getWritableDatabase();
		    ContentValues values;
		    values = new ContentValues();
	    	values.put(KEY_APP_NAME, gift.getName());
		    values.put(KEY_DAYS, gift.getDays());
		    values.put(KEY_RECEIVE, gift.getReceived());
		    values.put(KEY_SLUG, gift.getSlug());
		    values.put(KEY_TITLE, gift.getAppName());
		    values.put(KEY_ICON, gift.getPicture());
		    values.put(KEY_TIME_TO_RECIEVE, gift.getTimeToReceive());
		    db.insert(TABLE_GIFT, null, values);
		    db.close();
		}
	}
	
	public GiftSetting getGift(String name){
		String selectQuery = "SELECT * FROM " + TABLE_GIFT + " WHERE " + KEY_APP_NAME + " = '" + name + "'";
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    GiftSetting gift = null;
	    if (cursor.moveToFirst()) {
	        do {
	        	gift = new GiftSetting();
	            gift.setDays(cursor.getInt(cursor.getColumnIndex(KEY_DAYS)));
	            gift.setReceived(cursor.getInt(cursor.getColumnIndex(KEY_RECEIVE)));
	            gift.setName(name);
	            gift.setSlug(cursor.getString(cursor.getColumnIndex(KEY_SLUG)));
	            gift.setAppName(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
	            gift.setPicture(cursor.getString(cursor.getColumnIndex(KEY_ICON)));
	            gift.setTimeToReceive(cursor.getLong(cursor.getColumnIndex(KEY_TIME_TO_RECIEVE)));
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return gift;
	}
	
	public boolean checkGiftExists(String name){
		String selectQuery = "SELECT * FROM " + TABLE_GIFT;
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	String giftName = cursor.getString(cursor.getColumnIndex(KEY_APP_NAME));
	            if(giftName.equalsIgnoreCase(name)){
	            	return true;
	            }
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
		return false;
	}
	
	public void deleteGift(String name){
		SQLiteDatabase db = this.getWritableDatabase();
	    db.delete(TABLE_GIFT, KEY_APP_NAME + " = ?", new String[] { name });
	    db.close();
	}
	
	public List<GiftSetting> getAllPendingGift(){
		List<GiftSetting> giftList = new ArrayList<GiftSetting>();
		String selectQuery = "SELECT * FROM " + TABLE_GIFT + " WHERE " + KEY_DAYS + "!=0";
	    SQLiteDatabase db = this.getReadableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    GiftSetting gift;
	    if (cursor.moveToFirst()) {
	        do {
	        	gift = new GiftSetting();
	            gift.setDays(cursor.getInt(cursor.getColumnIndex(KEY_DAYS)));
	            gift.setReceived(cursor.getInt(cursor.getColumnIndex(KEY_RECEIVE)));
	            gift.setName(cursor.getString(cursor.getColumnIndex(KEY_APP_NAME)));
	            gift.setSlug(cursor.getString(cursor.getColumnIndex(KEY_SLUG)));
	            gift.setAppName(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
	            gift.setPicture(cursor.getString(cursor.getColumnIndex(KEY_ICON)));
	            gift.setTimeToReceive(cursor.getLong(cursor.getColumnIndex(KEY_TIME_TO_RECIEVE)));
	            giftList.add(gift);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return giftList;
	}
	
//	public boolean checkGiftSynced(){
//		String selectQuery = "SELECT * FROM " + TABLE_GIFT;
//	    SQLiteDatabase db = this.getWritableDatabase();
//	    Cursor cursor = db.rawQuery(selectQuery, null);
//	    int count = cursor.getCount();
//	    if(count == 0){
//	    	db.close();
//	    	return false;
//	    } else {
//	    	db.close();
//	    	return true;
//	    }
//	}
	
	public boolean updateGift(String name, String value){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_RECEIVE, value);
		int result = db.update(TABLE_GIFT, values, KEY_APP_NAME + "=" + name, null);
		if(result == 0){
			db.close();
			return false;
		} else {
			db.close();
			return true;
		}
	}
	
	public void syncInstalledApp(List<InstalledAppItem> list){
		SQLiteDatabase db = this.getWritableDatabase();
	    ContentValues values;
	    for(InstalledAppItem item : list){
	    	values = new ContentValues();
	    	values.put(KEY_PACKAGE_NAME, item.getPackageName());
		    values.put(KEY_APP_VERSION, item.getVersion());
		    db.insert(TABLE_INSTALLED_APPS, null, values);
	    }
	    db.close();
	}
	
	public void addInstalledApp(InstalledAppItem item){
		if(!checkAppInstalled(item.getPackageName())){
			SQLiteDatabase db = this.getWritableDatabase();
		    ContentValues values;
		    values = new ContentValues();
		    values.put(KEY_PACKAGE_NAME, item.getPackageName());
			values.put(KEY_APP_VERSION, item.getVersion());
			db.insert(TABLE_INSTALLED_APPS, null, values);
			db.close();
		}
	}
	
	public boolean checkSynced(){
		String selectQuery = "SELECT * FROM " + TABLE_INSTALLED_APPS;
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    int count = cursor.getCount();
	    cursor.close();
	    if(count == 0){
	    	db.close();
	    	return false;
	    } else {
	    	db.close();
	    	return true;
	    }
	}
	
	public boolean deleteInstalledApp(String packageName){
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_INSTALLED_APPS, KEY_PACKAGE_NAME + " = ?", new String[] { packageName });
	    db.close();
	    if(result == 0){
	    	return false;
	    } else {
	    	return true;
	    }
	}
	
	public boolean checkAppInstalled(String packageName){
		String selectQuery = "SELECT * FROM " + TABLE_INSTALLED_APPS;
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	String installed = cursor.getString(cursor.getColumnIndex(KEY_PACKAGE_NAME));
	            if(installed.equalsIgnoreCase(packageName)){
	            	return true;
	            }
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
		return false;
	}
	
	public boolean updateInstalledApp(String packageName, String version){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_APP_VERSION, version);
		int result = db.update(TABLE_INSTALLED_APPS, values, KEY_PACKAGE_NAME + "=" + packageName, null);
		if(result == 0){
			db.close();
			return false;
		} else {
			db.close();
			return true;
		}
	}
	
	public void addSearchQuery(String query){
		query = query.replace("'", "");
		String selectQuery = "SELECT * FROM " + TABLE_RECENT_SEARCH + " WHERE " + KEY_QUERY + " = '" + query + "'";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if(cursor.getCount() == 0){
			ContentValues values = new ContentValues();
		    values.put(KEY_QUERY, query);
		    db.insert(TABLE_RECENT_SEARCH, null, values);
		}
		cursor.close();
	    db.close();
	}
	
	public List<String> search(String query){
		List<String> itemList = new ArrayList<String>();
		String selectQuery = "SELECT * FROM " + TABLE_RECENT_SEARCH + " WHERE " + KEY_QUERY + " LIKE %" + query + "%";
		SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	String word = cursor.getString(0);
	            itemList.add(word);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return itemList;
	}
	
	public List<String> getAllRecentSearch(){
		List<String> itemList = new ArrayList<String>();
		String selectQuery = "SELECT * FROM " + TABLE_RECENT_SEARCH;
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	String query = cursor.getString(0);
	            itemList.add(query);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return itemList;
	}

	public boolean addDownloadLater(DownloadLaterItem item){
		String selectQuery = "SELECT * FROM " + TABLE_DOWNLOAD_LATER + " WHERE " + KEY_SLUG + " = '" + item.getSlug() + "'";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if(cursor.getCount() == 0){
			ContentValues values = new ContentValues();
		    values.put(KEY_SLUG, item.getSlug());
		    values.put(KEY_AUTHOR, item.getAuthor());
		    values.put(KEY_NAME, item.getAppName());
		    values.put(KEY_ICON, item.getImageUrl());
		    values.put(KEY_PRICE_1, String.valueOf(item.getPrice1()));
		    values.put(KEY_PRICE_2, String.valueOf(item.getPrice2()));
		    values.put(KEY_PACKAGE_NAME, item.getPackageName());
		    values.put(KEY_TYPE, item.getType());
		    db.insert(TABLE_DOWNLOAD_LATER, null, values);
		    cursor.close();
		    db.close();
		    return true;
		} else {
			cursor.close();
			db.close();
			return false;
		}
	}
	
	public void addDownloadedContent(ContentItem item){
		SQLiteDatabase db = this.getWritableDatabase();
	    ContentValues values = new ContentValues();
	    values.put(KEY_SLUG, item.getSlug());
	    values.put(KEY_TYPE, item.getType());
	    values.put(KEY_VERSION, item.getVersion());
	    values.put(KEY_TITLE, item.getFileName());
	    values.put(KEY_VERISON_NUMBER, item.getVersionNumber());
	    values.put(KEY_PATH, item.getPath());
	    db.insert(TABLE_DOWNLOADED_CONTENT, null, values);
	    db.close();
	}
	
	public boolean deleteDownloadedContent(String title){
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_DOWNLOADED_CONTENT, KEY_TITLE + " = ?", new String[] { title });
	    db.close();
	    if(result == 0){
	    	return false;
	    } else {
	    	return true;
	    }
	}
	
	public boolean deleteDownloadLater(String slug){
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_DOWNLOAD_LATER, KEY_SLUG + " = ?", new String[] { slug });
	    db.close();
	    if(result == 0){
	    	return false;
	    } else {
	    	return true;
	    }
	}
	
	public List<DownloadLaterItem> getDownloadLaterItem(String type){
		List<DownloadLaterItem> itemList = new ArrayList<DownloadLaterItem>();
		String selectQuery = "SELECT * FROM " + TABLE_DOWNLOAD_LATER + " WHERE " + KEY_TYPE + " = '" + type + "'";
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    DownloadLaterItem item;
	    if (cursor.moveToFirst()) {
	        do {
	        	item = new DownloadLaterItem();
	            item.setSlug(cursor.getString(cursor.getColumnIndex(KEY_SLUG)));
	            item.setAppName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
	            item.setAuthor(cursor.getString(cursor.getColumnIndex(KEY_AUTHOR)));
	            item.setImageUrl(cursor.getString(cursor.getColumnIndex(KEY_ICON)));
	            item.setPrice1(cursor.getInt(cursor.getColumnIndex(KEY_PRICE_1)));
	            item.setPrice2(cursor.getInt(cursor.getColumnIndex(KEY_PRICE_2)));
	            item.setPackageName(cursor.getString(cursor.getColumnIndex(KEY_PACKAGE_NAME)));
	            item.setType(cursor.getString(cursor.getColumnIndex(KEY_TYPE)));
	            if(checkAppInstalled(cursor.getString(cursor.getColumnIndex(KEY_PACKAGE_NAME)))){
	            	item.setInstalled(true);
	            } else {
	            	item.setInstalled(false);
	            }
	            itemList.add(item);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return itemList;
	}
	
	public List<ContentItem> getAllDownloadedContent(String type) {
	    List<ContentItem> list = new ArrayList<ContentItem>();
	    String selectQuery = "SELECT * FROM " + TABLE_DOWNLOADED_CONTENT + " WHERE " + KEY_TYPE + "='" + type + "'";
	 
	    SQLiteDatabase db = this.getWritableDatabase();
	    Cursor cursor = db.rawQuery(selectQuery, null);
	    if (cursor.moveToFirst()) {
	        do {
	        	ContentItem item = new ContentItem();
	            item.setSlug(cursor.getString(1));
	            item.setType(cursor.getString(2));
	            item.setVersion(cursor.getString(3));
	            item.setFileName(cursor.getString(5));
	            item.setPath(cursor.getString(6));
	            list.add(item);
	        } while (cursor.moveToNext());
	    }
	    cursor.close();
	    db.close();
	    return list;
	}
	
	public List<ContentItem> getToUpdate(String type){
		List<ContentItem> list = new ArrayList<ContentItem>();
	    String selectQuery = "SELECT " + KEY_SLUG + ",MAX(" + KEY_VERISON_NUMBER + ") FROM " + TABLE_DOWNLOADED_CONTENT + " WHERE " + KEY_TYPE + "='" + type + "' GROUP BY " + KEY_SLUG + "";
	    System.out.println(selectQuery);
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			while(!cursor.isAfterLast()){
				if(cursor.getInt(1) == 0){
					cursor.moveToNext();
				} else {
					ContentItem item = new ContentItem();
		            item.setSlug(cursor.getString(0));
		            item.setVersionNumber(cursor.getInt(1));
		            list.add(item);
		            cursor.moveToNext();
				}
			}
	    }
		cursor.close();
	    db.close();
	    return list;
	}
	
	public void closeDB(){
		this.getWritableDatabase().close();
	}
}
